package com.hjw.dynamicdatasource.util;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库元数据工具类，用于获取数据库表结构信息
 */
@Slf4j
@Component
public class DatabaseMetadataUtil {

    /**
     * 获取数据库中所有表的结构信息
     * @param dataSource 数据源
     * @param schema 数据库模式（可为空）
     * @return 表结构信息列表
     * @throws SQLException SQL异常
     */
    public List<TableInfo> getAllTables(DataSource dataSource, String schema) throws SQLException {
        List<TableInfo> tables = new ArrayList<>();
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            DatabaseMetaData metaData = conn.getMetaData();
            
            // 获取数据库产品名称，用于适配不同数据库
            String databaseProductName = metaData.getDatabaseProductName().toLowerCase();
            
            // 查询表信息
            ResultSet rs = metaData.getTables(conn.getCatalog(), schema, "%", new String[]{"TABLE"});
            
            while (rs.next()) {
                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(rs.getString("TABLE_NAME"));
                tableInfo.setTableSchema(rs.getString("TABLE_SCHEM"));
                tableInfo.setTableType(rs.getString("TABLE_TYPE"));
                tableInfo.setRemarks(rs.getString("REMARKS"));
                
                // 获取表的列信息
                List<ColumnInfo> columns = getTableColumns(metaData, tableInfo.getTableName(), schema, databaseProductName);
                tableInfo.setColumns(columns);
                
                tables.add(tableInfo);
            }
            rs.close();
            
        } catch (SQLException e) {
            log.error("获取数据库表结构失败", e);
            throw e;
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        
        return tables;
    }
    
    /**
     * 获取指定表的列信息
     * @param metaData 数据库元数据
     * @param tableName 表名
     * @param schema 数据库模式
     * @param databaseProductName 数据库产品名称
     * @return 列信息列表
     * @throws SQLException SQL异常
     */
    private List<ColumnInfo> getTableColumns(DatabaseMetaData metaData, String tableName, String schema, String databaseProductName) throws SQLException {
        List<ColumnInfo> columns = new ArrayList<>();
        
        // 根据数据库类型设置适当的schema参数
        String searchSchema = schema;
        if ("mysql".equals(databaseProductName)) {
            // MySQL使用catalog而不是schema
            searchSchema = null;
        }
        
        ResultSet rs = metaData.getColumns(metaData.getConnection().getCatalog(), searchSchema, tableName, "%");
        
        while (rs.next()) {
            ColumnInfo columnInfo = new ColumnInfo();
            columnInfo.setColumnName(rs.getString("COLUMN_NAME"));
            columnInfo.setDataType(rs.getInt("DATA_TYPE"));
            columnInfo.setTypeName(rs.getString("TYPE_NAME"));
            columnInfo.setColumnSize(rs.getInt("COLUMN_SIZE"));
            columnInfo.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
            columnInfo.setNullable(rs.getInt("NULLABLE") == 1);
            columnInfo.setRemarks(rs.getString("REMARKS"));
            columnInfo.setColumnDef(rs.getString("COLUMN_DEF"));
            
            columns.add(columnInfo);
        }
        rs.close();
        
        // 获取主键信息
        Map<String, Boolean> primaryKeys = getPrimaryKeys(metaData, tableName, schema);
        for (ColumnInfo column : columns) {
            if (primaryKeys.containsKey(column.getColumnName())) {
                column.setPrimaryKey(true);
            }
        }
        
        return columns;
    }
    
    /**
     * 获取表的主键信息
     * @param metaData 数据库元数据
     * @param tableName 表名
     * @param schema 数据库模式
     * @return 主键列名映射
     * @throws SQLException SQL异常
     */
    private Map<String, Boolean> getPrimaryKeys(DatabaseMetaData metaData, String tableName, String schema) throws SQLException {
        Map<String, Boolean> primaryKeys = new HashMap<>();
        
        ResultSet rs = metaData.getPrimaryKeys(metaData.getConnection().getCatalog(), schema, tableName);
        
        while (rs.next()) {
            String columnName = rs.getString("COLUMN_NAME");
            primaryKeys.put(columnName, true);
        }
        rs.close();
        
        return primaryKeys;
    }
    
    /**
     * 表信息类
     */
    @Data
    public static class TableInfo {
        private String tableName;
        private String tableSchema;
        private String tableType;
        private String remarks;
        private List<ColumnInfo> columns = new ArrayList<>();
    }
    
    /**
     * 列信息类
     */
    @Data
    public static class ColumnInfo {
        private String columnName;
        private int dataType;
        private String typeName;
        private int columnSize;
        private int decimalDigits;
        private boolean nullable;
        private String remarks;
        private String columnDef;
        private boolean primaryKey = false;
        
        // 获取对应的Java数据类型
        public String getJavaType() {
            switch (typeName.toUpperCase()) {
                case "INT":
                case "INTEGER":
                    return "Integer";
                case "BIGINT":
                    return "Long";
                case "VARCHAR":
                case "CHAR":
                case "TEXT":
                case "LONGVARCHAR":
                    return "String";
                case "DATE":
                    return "Date";
                case "DATETIME":
                case "TIMESTAMP":
                    return "java.util.Date";
                case "DECIMAL":
                case "NUMERIC":
                    return "BigDecimal";
                case "FLOAT":
                case "DOUBLE":
                    return "Double";
                case "BOOLEAN":
                case "BIT":
                    return "Boolean";
                default:
                    return "String";
            }
        }
    }
}